<?php
/**
 +------------------------------------------------------------------------------
 * ExAction控制器类
 +------------------------------------------------------------------------------
 * @category   SubAction
 * @package  app
 * @subpackage  Action
 * @author   朝闻道 <hydata@gmail.com>
 * @date 2010-9-1
 * @time  下午02:28:53
 +------------------------------------------------------------------------------
 */
class ExAction extends Action{
	protected function _initialize() {
		header("Content-Type:text/html; charset=utf-8");
	}
	
	/**
	   *导入指定文件名的Excel
	   *@date 2010-9-1
	   *@time 下午02:36:01
	   */
	function index() {
		//导入指定文件名的Excel
		if($_GET['act']=='dump'){
			$fname=$_REQUEST['fname'];
			$fpath='Public/exl/';
			$file=$fpath.$fname;
			if (!file_exists($file)) {
				exit("文件'$file'不存在.\n");
			}
			$data=S(md5($file));
			if(empty($data)){
				$this->error('读取缓存数据失败');
			}
			echo "<a href='".__URL__."/sdata/fname/{$fname}'>写入数据</a><br>";
			dump($data);
			exit();
		}
		
		error_reporting(E_ALL);
		
		import("@.Com.PHPExcel",'','.php');
		//import("@.Com.PHPExcel.IOFactory",'','.php');
		import("@.Com.PHPExcel.Reader.Excel5",'','.php');
		import("@.Com.PHPExcel.Reader.Excel2007",'','.php');
		import("ORG.Util.Image");
		$fname=$_REQUEST['fname'];
		$fpath='Public/exl/';
		$file=$fpath.$fname;
		if (!file_exists($file)) {
			exit("文件'$file'不存在.\n");
		}
		
		$PHPExcel = new PHPExcel();    
		$PHPReader = new PHPExcel_Reader_Excel5();   

		
		//$objPHPExcel=PHPExcel_IOFactory::load($file);
		$objPHPExcel=$PHPReader->load($file);
		
		//echo date('H:i:s') . " Write to Excel2007 format\n";
		$sheet = $objPHPExcel->getSheet(0); // 读取第一個工作表(编号从 0 开始) 
		
		$highestRow = $sheet->getHighestRow(); // 取得总行数 
		$highestColumn = $sheet->getHighestColumn(); // 取得总列数  
		
		$arr = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M', 14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');
		
		//导入数据
		$data=array();
		if($_GET['act']=='test'){
			$t=$sheet->getCellByColumnAndRow(4,5)->getValue();
			dump($t);
			exit();
		}
		//dump($sheet->getCell('Q22')->getValue());
		$temp='';
		for ($row =3; $row <= $highestRow; $row++) { 
	        //$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
	        //$val = iconv("utf-8","gb2312",$val);
	        $xcid=$sheet->getCellByColumnAndRow(1,$row)->getValue();
	        $cid=empty($xcid)?$cid:$xcid;
	        $data[$row]['cid']=$cid;
	        $x=$sheet->getCellByColumnAndRow(2,$row)->getValue();
	        $data[$row]['exl_id']=$x;
	        if(!empty($x)){
		        $xnum=xnum($x);
	        }/*else{
	        	$xnum=$temp;
	        }*/
	        $data[$row]['xnum']=$xnum;
	        //$temp=$xnum;
	        $data[$row]['lang']=trim($sheet->getCellByColumnAndRow(3,$row)->getValue());
	        $data[$row]['scope']=nl2br($sheet->getCellByColumnAndRow(5,$row)->getValue());
	        $data[$row]['about']=nl2br($sheet->getCellByColumnAndRow(6,$row)->getValue());
	        $data[$row]['product']=trim($sheet->getCellByColumnAndRow(7,$row)->getValue());
	        $data[$row]['ename']=trim($sheet->getCellByColumnAndRow(11,$row)->getValue());
	        $data[$row]['cname']=trim($sheet->getCellByColumnAndRow(12,$row)->getValue());
	        $data[$row]['showroom']=$this->furl($sheet->getCellByColumnAndRow(19,$row)->getValue());
	        $data[$row]['homepage']=$this->furl($sheet->getCellByColumnAndRow(20,$row)->getValue());
	        $data[$row]['postcode']=trim($sheet->getCellByColumnAndRow(21,$row)->getValue());
	        $data[$row]['contact_person']=trim($sheet->getCellByColumnAndRow(18,$row)->getValue());
	        $data[$row]['email']=trim($sheet->getCellByColumnAndRow(8,$row)->getValue());
	        $data[$row]['phone']=trim($sheet->getCellByColumnAndRow(16,$row)->getValue());
	        $data[$row]['fax']=trim($sheet->getCellByColumnAndRow(17,$row)->getValue());
	        $data[$row]['cnadd']=trim($sheet->getCellByColumnAndRow(14,$row)->getValue());
	        $data[$row]['enadd']=trim($sheet->getCellByColumnAndRow(13,$row)->getValue());
	        
	        foreach ($data[$row] as $d){
	        	if(is_object($d)){
	        		echo "数据表读取出错！";
	        		dump($data[$row]);
	        		exit();
	        	}
	        }
		}
		
		//echo nl2br($sheet->getCellByColumnAndRow(4,3)->getValue());
		
/*		//导入栏目
		$text='';
		for ($row=2;$row<=$highestRow;$row++){
			$id=$sheet->getCellByColumnAndRow(0,$row)->getValue();
			$ename=$sheet->getCellByColumnAndRow(2,$row)->getValue();
			$cname=$sheet->getCellByColumnAndRow(3,$row)->getValue();
			$pid=$sheet->getCellByColumnAndRow(1,$row)->getValue();
			if(!is_numeric($pid)){
				$ename=$pid;
				$pid='2';
			}
			$time=time();
			$text.='INSERT INTO `iic_cat` (`id`, `pid`, `topid`, `ename`, `cname`, `description`, `keyword`, `rank`, `ctime`, `is_show`) VALUES ';
			$text.="($id, '$pid', '2','$ename', '$cname', '', '', '50', '$time', '1');<br>\t\n\r";
		}
		echo $text;*/
		$x=S(md5($file),$data);
		if($data){
			echo "数据读取成功！<br>";
			echo "<a href='".__URL__."/index/fname/{$fname}/act/dump'>查看数据</a><br>";
			echo "<a href='".__URL__."/sdata/fname/{$fname}'>写入数据</a><br>";
		}else{
			echo "数据读取不成功！<br>";
		}
		
		
	}//end index
	
	/**
	   *写入数据
	   *@date 2010-9-2
	   *@time 下午04:14:27
	   */
	function sdata() {
		//写入数据
		$fname=$_REQUEST['fname'];
		$fpath='Public/exl/';
		$file=$fpath.$fname;
		if (!file_exists($file)) {
			exit("文件'$file'不存在.\n");
		}
		$data=S(md5($file));
		if(empty($data)){
			$this->error('读取缓存数据失败');
		}
		//dump($data);
		$arc=D("Arc");
		$arclang=D("ArcLang");
		$err='';
		$edata=array();
		$ok=0;
		$error=0;
		$t=time();
		foreach ($data as $k=>$v){
			$info=$arc->where("xnum='{$v['xnum']}'")->find();
			if($info){
				$aid=$info['id'];
			}else{
				$vo=array();
				$v['cid']='117';
				$v['szm']=self::get_letter($v['ename']).','.self::get_letter($v['cname']);
				$v['olang']='';
				if(!empty($v['ename'])){
					$v['olang'].='en-us'.',';
				}
				if(!empty($v['cname'])){
					$v['olang'].='zh-cn';
				}
				$vo['cid']=$v['cid'];
				$vo['szm']=$v['szm'];
				$vo['olang']=$v['olang'];
				$vo['ename']=$v['ename'];
				$vo['cname']=$v['cname'];
				$vo['xnum']=$v['xnum'];
				$vo['homepage']=$v['homepage'];
				$vo['email']=$v['email'];
				$vo['ctime']=$t;
				$vo['is_show']=1;
				$vo['password']=ptime();
				$vo['click']=0;
				$vo['shownum']=0;
				$vo['activetime']=$t;
				$vo['exl_id']=$v['exl_id'];
				
				//$arc_vo=$arc->create($v);
				$aid=$arc->add($vo);
			}
			if($aid){
				if($arccnid&&strtolower($v['lang'])=='cn'){//独立的附加中文信息
					$v['lang']='zh-cn';
					$arccnid=$arclang->where("aid=$aid,lang='zh-cn'")->find();
					$arccnid['about']=$v['about'];
					$arccn->save($arccnid);
					$this->pro($v['product'],$aid,$v['lang']);
				}else{//英文信息
					if(strtolower($v['lang'])=='en'){
						$en_vo=array();
						$en_vo['lang']='en-us';
						$en_vo['aid']=$aid;
						$en_vo['sitename']=$v['ename'];
						$en_vo['scope']=$v['scope'];
						$en_vo['about']=$v['about'];
						$en_vo['add']=$v['enadd'];
						$en_vo['showroom']=$v['showroom'];
						$en_vo['keywords']=$v['keywords'];
						$en_vo['description']=$v['description'];
						$en_vo['postcode']=$v['postcode'];
						$en_vo['phone']=$v['phone'];
						$en_vo['fax']=$v['fax'];
						$en_vo['contact_person']=$v['contact_person'];
						$en_vo=$arclang->create($en_vo);
						$enid=$arclang->add($en_vo);
						$cn_vo=array();
						$cn_vo['aid']=$aid;
						$cn_vo['add']=$v['cnadd'];
						$cn_vo['sitename']=$v['cname'];
						$cn_vo['lang']='zh-cn';
						$cn_vo=$arclang->create($cn_vo);
						$cnid=$arclang->add($cn_vo);
					}else{
						$cn_vo=array();
						$cn_vo['aid']=$aid;
						$cn_vo['lang']='zh-cn';
						$cn_vo['sitename']=$v['cname'];
						$cn_vo['scope']=$v['scope'];
						$cn_vo['about']=$v['about'];
						$cn_vo['add']=$v['cnadd'];
						$cn_vo['showroom']=$v['showroom'];
						$cn_vo['keywords']=$v['keywords'];
						$cn_vo['description']=$v['description'];
						$cn_vo['postcode']=$v['postcode'];
						$cn_vo['phone']=$v['phone'];
						$cn_vo['fax']=$v['fax'];
						$cn_vo['contact_person']=$v['contact_person'];
						$cn_vo=$arclang->create($cn_vo);
						$cnid=$arclang->add($en_vo);
						$en_vo=array();
						$en_vo['aid']=$aid;
						$en_vo['lang']='en-us';
						$en_vo['add']=$v['enadd'];
						$en_vo['sitename']=$v['ename'];
						$en_vo=$arclang->create($en_vo);
						$enid=$arclang->add($en_vo);
					}
					$v['lang']=strtolower($v['lang'])=='cn'?'zh-cn':'en-us';
					$this->pro($v['product'],$aid,$v['lang']);
				}
				/*checkdnsrr($vo['email'])
				$user=array("email"=>$vo['email'],'aid'=>$aid,'password'=>$vo['password']);
				
				$uid=self::create_member($user);*/
				$arc->where("id='{$aid}'")->save(array('master'=>$uid));
				$ok++;
				$err.=$v['exl_id'].'写入成功！<br>';
			}else{
				$error++;
				$edata[]=$v;
				$err.='<b>'.$v['exl_id'].'写入有错！'.$arc->getlastsql().'</b><br>';
				break;
			}
		}
		echo '成功:'.$ok.'--------失败:'.$error.'<br><br>';
		echo $err;
		//dump($edata);
	}//end sdata
	
	
	/**
	   *写入产品表
	   *@date 2010-9-2
	   *@time 下午03:13:29
	   */
	protected function pro($str='',$aid='',$lang='') {
		//写入产品表
		if(!empty($str)&&is_numeric($aid)){
			$arr=explode('||',$str);
			$dao=D("Product");
			$return['ok']=0;
			$return['error']=0;
			foreach ($arr as $v){
				$data=array();
				$data['aid']=$aid;
				$data['plang']=strtolower($lang);
				$data['ptitle']=$v;
				$data['pis_show']='1';
				$data=$dao->create($data);
				$id=$dao->add($data);
				if($id){
					$return['ok']++;
				}else{
					$return['error']++;
				}
			}
			return $return;
		}else{
			return false;
		}
	}//end pro
	
	/**
	   *规格化URL
	   *@date 2010-9-3
	   *@time 下午05:38:23
	   */
	function furl($url) {
		//规格化URL
		if(empty($url)) return '#';
		$arr=explode('/',$url);
		if($arr['0']!='http:'){
			$url='http://'.trim(trim($url,'/'));
		}else{
			$url=trim(trim($url,'/'));
		}
		return $url;
	}//end function_name
	
	/**
	 * 取首字母
	 * Enter description here ...
	 * @param unknown_type $input
	 */
	function get_letter($input) {
		if(empty($input)){
			return '';
		}else{
			$dict = array ('a' => 0xB0C4, 'b' => 0xB2C0, 'c' => 0xB4ED, 'd' => 0xB6E9, 'e' => 0xB7A1, 'f' => 0xB8C0, 'g' => 0xB9FD, 'h' => 0xBBF6, 'j' => 0xBFA5, 'k' => 0xC0AB, 'l' => 0xC2E7, 'm' => 0xC4C2, 'n' => 0xC5B5, 'o' => 0xC5BD, 'p' => 0xC6D9, 'q' => 0xC8BA, 'r' => 0xC8F5, 's' => 0xCBF9, 't' => 0xCDD9, 'w' => 0xCEF3, 'x' => 0xD188, 'y' => 0xD4D0, 'z' => 0xD7F9 );
			$str_1 = substr ( $input, 0, 1 );
			if ($str_1 >= chr ( 0x81 ) && $str_1 <= chr ( 0xfe )) {
				$num = hexdec ( bin2hex ( substr ( $input, 0, 2 ) ) );
				foreach ( $dict as $k => $v ) {
					if ($v >= $num)
						break;
				}
				return strtolower($k);
			} else {
				return strtolower($str_1);
			}
		}
	}
	
		/**
	   *写入一个用户
	   *@date 2010-12-7 / @time 下午01:50:59
	   *@param $email,$aid
	   */
	protected function create_member($user=array()) {
		//写入一个用户
		$dao=D("Member");
		$uid=$dao->add($user);
		if($uid){
			return $uid;
		}else return false;
	}//end create_member
}//end ExAction